<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * Static MySQL statement
 */
require_once 'library/spica/core/datasource/db/mysql/Statement.php';

/**
 * Utility class to help generate and execute migration statements without writing SQL.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.3
 * @since      December 14, 2009
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: MigrationStatement.php 1743 2010-03-30 18:41:53Z pcdinh $
 */
class SpicaMySQLMigrationStatement extends SpicaMySQLStatement
{
	/**
	 * Map named types to native types.
	 *
	 * @var array
	 */
	public $nativeDatabaseTypes = array(
      'primary_key' => 'int(11) unsigned NOT NULL auto_increment PRIMARY KEY',
      'string' => 'varchar(255)',
      'text' => 'text',
      'integer' => 'int(11)',
      'float' => 'float',
      'datetime' => 'datetime',
      'timestamp' => 'datetime',
      'time' => 'datetime',
      'date' => 'date',
      'binary' => 'blob',
      'boolean' => 'tinyint(1)',
	);

	/**
	 * Recreates a database with a specified name.
	 *
	 * @param string $name
	 * @throws SpicaDatabaseException
	 * @return bool
	 */
	public function recreateDatabase($name)
	{
		$this->deleteDatabase($name);
		$this->createDatabase($name);
	}

	/**
	 * Deletes a database specified by a name.
	 *
	 * @param string $name
	 * @throws SpicaDatabaseException
	 * @return bool
	 */
	public function deleteDatabase($name)
	{
		$this->execute('DROP DATABASE '.$name);
	}

	/**
	 * Creates a database with a specified name.
	 *
	 * @throws SpicaDatabaseException
	 * @param string $name	
	 * @return bool
	 */
	public function createDatabase($name)
	{
		$this->execute('CREATE DATABASE '.$name);
	}

	/**
	 * Creates a table in this database
	 *
	 * @param string $name Table name
	 * @param array  $columns Column definitions {string column_name => {string 'type' => string named_type, string 'options' => {...}}, ...}
	 * @return bool
	 */
	public function createTable($name, $columns = array())
	{
		$this->execute("CREATE TABLE $name (id {$this->nativeDatabaseTypes['primary_key']})");

		foreach ($column as $columnName => $params)
		{
			$this->addColumn($name, $columnName, $params['type'], $params['options']);
		}
	}

	/**
	 * Deletes a table from this database (DROP TABLE)
	 *
	 * @throws SpicaDatabaseException
	 * @param  string $name Table name
	 * @return bool
	 */
	public function deleteTable($name)
	{
		$this->execute('DROP TABLE '.$name);
	}

	/**
	 * Adds a column to a table in this database
	 *
	 * <b>Available options for <samp>$options</samp>:</b>
	 * - <i>limit:</i> Limiting the length, or specifying enums. (ie. 255 or "'t','f'")
	 * - <i>unsigned:</i> Boolean value, only valid if $type == 'integer'. Defaults to false.
	 * - <i>null:</i> Boolean value. If false, NOT NULL will be used in description, forcing a non-null
	 * value for inserts. Defaults to true.
	 * - <i>default:</i> Default value to be used on inserts w/o a value for this column. (ie. "Untitled")
	 *
	 * @param string $tableName
	 * @param string $columnName
	 * @param string $type one of the keys in $this->nativeDatabaseTypes
	 * @param array $options
	 * @throws SpicaDatabaseException
	 * @return bool
	 */
	public function addColumn($tableName, $columnName, $type, $options = array())
	{
		$sql = "ALTER TABLE $tableName ADD $columnName ".$this->nativeDatabaseTypes[$type];

		if (isset($options['limit']))
		{
			$sql.= '('.$options['limit'].')';
		}

		if (isset($options['unsigned']) && $options['unsigned'] && $type == 'integer')
		{
			$sql.= ' unsigned';
		}

		if (isset($options['null']) && !$options['null'])
		{
			$sql.= ' NOT NULL';
		}

		if (isset($options['default']))
		{
			$sql.= " DEFAULT '{$options['default']}'";
		}

		$this->execute($sql);
	}

	/**
	 * Deletes a column from a table
	 *
	 * @throws SpicaDatabaseException
	 * @param string
	 * @param string
	 */
	public function deleteColumn($tableName, $columnName)
	{
		$this->execute('ALTER TABLE '.$tableName.' DROP `'.$columnName.'`');
	}

    /**
     * Converts a primary key to a index key.
     *
     * Useful for partitioning tables whose keys in partition is not part of primary/unique key.
     *
     * @throws SpicaDatabaseException
     * @param  string $table
     * @param  string $pk
     * @param  string $indexName
     */
    public function convertPrimaryKeyToKey($table, $pk, $indexName = null)
    {
        if (null === $indexName)
        {
            $indexName = 'idx_'.$pk;
        }

        $this->execute('ALTER TABLE `'.$table.'` DROP PRIMARY KEY, ADD KEY '.$indexName.' ('.$pk.')');
    }

    /**
     * Moves the current table content to another table and make the current table empty.
     * Acts just like logrotated for rotating log files.
     * 
     * @param string $table Table to rotate
     * @param string $rotateTo The table that stores the current table content
     */
    public function rotateTable($table, $rotateTo)
    {
        $this->execute('CREATE TABLE '.$table.'_new LIKE '.$table);
        $this->execute('RENAME TABLE '.$table.' TO '.$rotateTo.', '.$table.'_new TO '.$table);
    }

    /**
     * Removes duplicate rows from a table.
     * 
     * @param string $table
     * @param string $column Name of the column which contains duplicated values
     */
    public function removeDuplicates($table, $column)
    {
        $this->execute('CREATE TABLE '.$table.'_new AS SELECT * FROM '.$table.' WHERE 1 GROUP BY '.$column);
        $this->execute('DROP TABLE '.$table);
        $this->execute('RENAME TABLE '.$table.'_new TO '.$table);
    }
}

?>